{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "view-in-github"
   },
   "source": [
    "<a href=\"https://colab.research.google.com/github/peremartra/Large-Language-Model-Notebooks-Course/blob/main/P1-NL2SQL/NL2SQL_OpenAI_Azure.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "Paa52inqag9w"
   },
   "source": [
    "<div align=\"center\">\n",
    "<h1><a href=\"https://github.com/peremartra/Large-Language-Model-Notebooks-Course\">Learn by Doing LLM Projects</a></h1>\n",
    "    <h3>Understand And Apply Large Language Models</h3>\n",
    "    <h2>Natural Language to SQL with LLMs</h2>\n",
    "    <h3>Inferencing the NL2SQL on Azure</h3>\n",
    "    by <b>Pere Martra</b>\n",
    "</div>\n",
    "\n",
    "<br>\n",
    "\n",
    "<div align=\"center\">\n",
    "    &nbsp;\n",
    "    <a target=\"_blank\" href=\"https://www.linkedin.com/in/pere-martra/\"><img src=\"https://img.shields.io/badge/style--5eba00.svg?label=LinkedIn&logo=linkedin&style=social\"></a>\n",
    "    \n",
    "</div>\n",
    "\n",
    "<br>\n",
    "<hr>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "uxFVaxlskUf-"
   },
   "source": [
    "In this notebook, we make a call to a GPT3.5 model that we've set up from Azure OpenAI Studio to function as a translator for SQL queries from natural language.\n",
    "\n",
    "The prompt structure has been created and explained in a [previous article](https://colab.research.google.com/drive/1mbIAv1p6nqtsngXfCG5WrozlvmoJI06F#scrollTo=uxFVaxlskUf-&line=3&uniqifier=1), part of the Large Language Models course."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "Jssm3zDHbKq-"
   },
   "outputs": [],
   "source": [
    "#Install openai\n",
    "!pip install -q \"openai<1.0.0\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "ZQ48GZBFaaHg"
   },
   "outputs": [],
   "source": [
    "import os\n",
    "import openai"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "ylH7CZ75nj53"
   },
   "source": [
    "\n",
    "You can Obtain the **prompt**  the **api_base** and the **key** from the Sample Code window on Chat Playground in Azure Open Studio.\n",
    "\n",
    "![Azure_OpenAI_Code.png]()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "KDPp3PHGnZ7e"
   },
   "outputs": [],
   "source": [
    "#Azure configuration\n",
    "openai.api_type = \"azure\"\n",
    "#Here the inference point that you can get from Azure\n",
    "openai.api_base = \"https://openaicourseperemartra.openai.azure.com/\"\n",
    "openai.api_version = \"2023-07-01-preview\"\n",
    "\n",
    "openai.api_key = \"azurekey\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "VQeyT_dCbGND"
   },
   "outputs": [],
   "source": [
    "context = [ {'role':'system', 'content':\"\"\"\n",
    "    create table employees(\\n        ID_Usr INT primary key,\\n        name VARCHAR);\\n    /*3 example rows\\n    select * from employees limit 3;\\n    ID_Usr    name\\n    1344      George StPierre\\n    2122      Jon jones\\n    1265      Anderson Silva\\n    */\\n\\n    create table salary(\\n        ID_Usr INT,\\n        year DATE,\\n        salary FLOAT,\\n        foreign key (ID_Usr) references employees(ID_Usr));\\n    /*3 example rows\\n    select * from salary limit 3\\n    ID_Usr    date          salary\\n    1344      01/01/2023    61000\\n    1344      01/01/2022    60000\\n    1265      01/01/2023    55000\\n    */\\n\\n    create table studies(\\n        ID_study INT,\\n        ID_Usr INT,\\n        educational_level INT,  /* 5=phd, 4=Master, 3=Bachelor */\\n        Institution VARCHAR,\\n        Years DATE,\\n        Speciality VARCHAR,\\n        primary key (ID_study, ID_Usr),\\n        foreign key(ID_Usr) references employees (ID_Usr));\\n    /*3 example rows\\n    select * from studies limit 3\\n    ID_Study ID_Usr educational_level Institution    Years       Speciality\\n    2782     1344   3                 UC San Diego   01/01/2010  Bachelor of Science in Marketing\\n    2334     1344   5                 MIT            01/01/2023  Phd. Data Science.\\n    2782     2122   3                 UC San Diego   01/01/2010  Bachelor of Science in Marketing\\n    */\\n\\n-Maintain the SQL order simple and efficient as you can, using valid SQL Lite, answer the following questions for the table provided above. \\nQuestion: How Many employes we have with a salary bigger than 50000?\"},{\"role\":\"user\",\"content\":\"How Many employes we have with a salary bigger than 50000?\"},{\"role\":\"assistant\",\"content\":\"SELECT COUNT(*) AS total_employees\\nFROM employees e\\nINNER JOIN salary s ON e.ID_Usr = s.ID_Usr\\nWHERE s.salary > 50000;\"},{\"role\":\"user\",\"content\":\"Return the names of the three people who have had the highest salary increase in the last three years.\"},{\"role\":\"assistant\",\"content\":\"SELECT e.name\\nFROM employees e\\nJOIN salary s ON e.ID_usr = s.ID_usr\\nWHERE s.year >= DATE_SUB(CURDATE(), INTERVAL 3 YEAR)\\nGROUP BY e.name\\nORDER BY (MAX(s.salary) - MIN(s.salary)) DESC\\nLIMIT 3;\n",
    "\"\"\"} ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "BcpmXLF1cVGb"
   },
   "outputs": [],
   "source": [
    "#Functio to call the model.\n",
    "def return_CCRMSQL(user_message, context):\n",
    "\n",
    "    newcontext = context.copy()\n",
    "    newcontext.append({'role':'user', 'content':\"question: \" + user_message})\n",
    "\n",
    "    response = openai.ChatCompletion.create(\n",
    "      engine=\"GPT3NL2SQLSample1Deployment\", #Our deployment\n",
    "      messages = newcontext,\n",
    "      temperature=0,\n",
    "      max_tokens=800)\n",
    "\n",
    "    return (response.choices[0].message[\"content\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "VmB27XTWhKOn",
    "outputId": "f0bd7532-d52f-47dd-87fc-f307183fe772"
   },
   "outputs": [],
   "source": [
    "context_user = context.copy()\n",
    "print(return_CCRMSQL(\"The name of the employee best paid\", context_user))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "svMFswMghM3D",
    "outputId": "adbdbe12-0a72-4e37-8e4b-a132eb5993d2"
   },
   "outputs": [],
   "source": [
    "print(return_CCRMSQL(\"Return the Institution with a higher average salary\", context_user))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "CxcnI77oqLpl"
   },
   "source": [
    "#Conclusions.\n",
    "Once you have the prompt to use, set up the OpenAI services in Azure is straightforward.\n",
    "\n",
    "I observed some differences in the SQL from the one obtained calling directly to OpenAI API.\n",
    "\n",
    "But both are correct, I can't explain why these differences."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "YMVK994yhbnP"
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "colab": {
   "authorship_tag": "ABX9TyN6Lg+o+luu83A6DEC/g36h",
   "include_colab_link": true,
   "provenance": []
  },
  "kernelspec": {
   "display_name": "Python 3",
   "name": "python3"
  },
  "language_info": {
   "name": "python"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}
